package com.glodon.paas.job.monitor.dao;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import com.glodon.paas.job.extend.spring.AutowaireHibernateDAOSupport;
import com.glodon.paas.job.model.GlobalStatus;
import com.glodon.paas.job.model.MonitorStatus;
import com.glodon.paas.job.model.Task;
import com.glodon.paas.job.monitor.model.MonitorQueryDTO;

@Repository
public class MonitorStatusDAO extends AutowaireHibernateDAOSupport {

    public void save(MonitorStatus monitorStatus) {
        getHibernateTemplate().saveOrUpdate(monitorStatus);
    }

    public MonitorStatus get(long id) {
        return getHibernateTemplate().get(MonitorStatus.class, id);
    }

    @SuppressWarnings("unchecked")
    public List<MonitorStatus> getByType(String type) {
        return getHibernateTemplate().find("from MonitorStatus s where s.type = ? order by s.traceId, s.traceIndex",
                type);
    }

    @SuppressWarnings("unchecked")
    public List<MonitorStatus> getByType(String type, Date date) {
        return getHibernateTemplate().find("from MonitorStatus s where s.type = ? and s.updatedTime > ? order by s.traceId, s.traceIndex",
                type, date);
    }

    public GlobalStatus getGlobalStatus() {
        return getHibernateTemplate().get(GlobalStatus.class, "global");
    }

    public void save(GlobalStatus globalStatus) {
        getHibernateTemplate().saveOrUpdate(globalStatus);
    }

    @SuppressWarnings("unchecked")
    public List<MonitorStatus> getByTypeAndTraceId(String type, String traceId) {
        return (List<MonitorStatus>) getHibernateTemplate().find(
                "from MonitorStatus s where s.type = ? and s.traceId = ?", type, traceId);
    }

    @SuppressWarnings("unchecked")
    public MonitorStatus getByTypeAndTraceIdNotIndex(String type, String traceId) {
        List<MonitorStatus> list = getHibernateTemplate().find(
                "from MonitorStatus s where s.type = ? and s.traceId = ? and s.traceIndex is null", type, traceId);
        if (list.size() == 0)
            return null;
        if (list.size() > 1)
            throw new RuntimeException("error get MonitorStatus by type and trace id, type: " + type + ", traceId: "
                    + traceId);
        return list.get(0);
    }

    //select job details
    @SuppressWarnings("unchecked")
    public List<Object[]> getJobDetailsByType(String type, String jobId, String date,int fromNum,int pageNum) {
        Session session = getHibernateTemplate().getSessionFactory().openSession();
        String sql="";
        if(fromNum==0&&pageNum==0){
        	 sql = "select s.id,s.cmd,s.created_time,s.json,s.trace_id,s.trace_index,s.type,s.updated_time,s.task_id,t.id,t.endTime,t.startTime,t.status,t.taskInfo,t.taskType,t.create_time  from monitor_status s, task t where s.type = '"
                 + type + "' and s.task_Id = t.id ";
        	
        }else{
        	 sql = "select s.id,s.cmd,s.created_time,s.json,s.trace_id,s.trace_index,s.type,s.updated_time,s.task_id,t.id,t.endTime,t.startTime,t.status,t.taskInfo,t.taskType,t.create_time  from (select * from monitor_status where trace_id in(select * from (select distinct trace_id from monitor_status where type='"+type+"' order by created_time desc limit "+fromNum+","+pageNum+")s3) )  s, task t where  s.task_Id = t.id ";
        }
       
        if (StringUtils.isNotBlank(jobId)) {
            sql += "and s.trace_id='" + jobId.trim() + "'";
        }
        if (StringUtils.isNotBlank(date)) {
            sql += " and t.startTime>='" + date.trim() + "'";
        }
        sql += " order by s.trace_Id,t.EndTime,s.task_id";
        List<Object[]> resultList = session.createSQLQuery(sql).list();
        session.close();
        return resultList;
    }

    @SuppressWarnings("unchecked")
    public List<Object[]> getTaskDetailsByType(String type, String id, String date,int fromNum,int pageNum) {
        Session session = getHibernateTemplate().getSessionFactory().openSession();
        String sql = "select t.id,t.endTime,t.startTime,t.status,t.taskInfo,t.taskType,t.create_time,t.jobserver_sumbitTime from task t where 1=1 ";
        if (StringUtils.isNotBlank(id)) {
            sql += "and t.id='" + id.trim() + "'";
        }
        if (StringUtils.isNotBlank(date)) {
            sql += " and t.jobserver_sumbitTime>='" + date.trim()+ "'";
        }
        
        if(fromNum!=0||pageNum!=0){
        	 sql+=" order by t.jobserver_sumbitTime desc  limit "+fromNum+","+pageNum;
        }
       
        List<Object[]> resultList = session.createSQLQuery(sql).list();
        session.close();
        return resultList;
    }
    
	@SuppressWarnings("unchecked")
	public List<Object[]> getJobDetailsById(String type, String jobId) {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
//		String sql = "select * from task t1 where t1.id in( select distinct s.task_id FROM monitor_status s, task t where s.task_id = t.id  and s.type='"+type+"'and s.trace_id='"+jobId+"')";
		String sql =" SELECT T.* FROM (select distinct s.task_id FROM monitor_status s, task t   where s.task_id = t.id  and s.type='"+type+"' and s.trace_id='"+jobId+"') S3 LEFT JOIN TASK T ON S3.task_id=T.id";
		List<Object[]> resultList = session.createSQLQuery(sql).list();
		session.close();
		return resultList;
	}
	public List<MonitorStatus> getUnavalibalAgent(int threshold){
		 return getHibernateTemplate().find("from MonitorStatus s where s.type = 'agent_status' and now()-s.updatedTime>="+threshold*60*1000);
	}
	public int getWaitingTaskNumber(){
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		String sql = "select count(*) as count from task s where s.status='created'";
		List<Long> resultList = session.createSQLQuery(sql).list();
		session.close();
		return ((Number)resultList.get(0)).intValue();
//		 return ((Number)(getHibernateTemplate().find("select count(*) as count from Task s where s.status='created'").iterator().next())).intValue();
	}
	public int getAvgWaitingTime(){
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		String sql = "select avg(starttime-create_time) as avg from task";
		List<Double> resultList = session.createSQLQuery(sql).list();
		session.close();
		return ((Double)resultList.get(0)).intValue();
//		return ((Number)(getHibernateTemplate().find("select avg(s.startTime-s.createTime) as avg from Task ").iterator().next())).intValue();
	}
	
    public List<Object[]> queryTaskList(MonitorQueryDTO taskQuery) {
        Session session = getHibernateTemplate().getSessionFactory().openSession();
        String sql = "SELECT t.id,t.endTime,t.startTime,t.status,t.taskInfo,t.taskType,t.create_time,t.jobserver_sumbitTime," +
       	    		"t.originFilename FROM  task t WHERE 1=1 ";
        
        if (StringUtils.isNotBlank(taskQuery.getId())) {
            sql += " and t.id='" + taskQuery.getId().trim() + "'";
        }
        if (StringUtils.isNotBlank(taskQuery.getFromDate())) {
            sql += " and t.create_time >='" + taskQuery.getFromDate().trim()+ "'";
        }
        if (StringUtils.isNotBlank(taskQuery.getToDate())) {
            sql += " and t.create_time <='" + taskQuery.getToDate().trim()+ "'";
        }
        if (StringUtils.isNotBlank(taskQuery.getFileName())) {
            sql += " and t.originFilename like '%" + taskQuery.getFileName().trim()+ "%'";
        }
        if (StringUtils.isNotBlank(taskQuery.getType())) {
            sql += " and t.taskType like '" + taskQuery.getType().trim()+ "%'";
        }
        sql+=" order by t.create_time desc,t.jobserver_sumbitTime desc limit "+taskQuery.getFromNum()+","+taskQuery.getPageNum();
        List<Object[]> resultList = session.createSQLQuery(sql).list();
        session.close();
        return resultList;
    }
}
